Create sequence Get_ID_SEQUENCE
  minvalue 1
  maxvalue 999999999999999999999999999
  increment by 1
  start with 1
  cache 20
  noorder
  nocycle;
  
/*----------------------CREATE FEEDBACK ENTITY---------------------*/
Create table FeedBack_TABLE
(
	ID number(*,0),
	Message varchar2(200),
	FromUser varchar2(20),
	DateMessage Number(15),
	Page varchar2(20)
);

create or replace trigger  BI_FeedBack_TABLE
  before insert on FeedBack_TABLE
  for each row
begin
  select Get_ID_SEQUENCE.nextval into :new.ID from dual;
end;
/

alter trigger BI_FeedBack_TABLE enable;
/*----------------------CREATE USER STATE VALUE OBJECT---------------------*/
Create table UserState_TABLE
(
  ID number(*,0),
  isBanned number(1) check (isBanned = 0 OR isBanned = 1),
  SessionID varchar2(32),
  LastIP varchar2(32),
  constraint PK_UserState primary key (Id),
  constraint U_UserState unique(isBanned, SessionID, LastIP)
);

create or replace trigger  BI_UserState_TABLE
  before insert on UserState_TABLE
  for each row
begin
  select Get_ID_SEQUENCE.nextval into :new.ID from dual;
end;
/

alter trigger BI_UserState_TABLE enable;

/*----------------------CREATE USER INFO VALUE OBJECT---------------------*/
Create table UserInfo_TABLE
(
  ID number(*,0),
  Login varchar2(20) unique not null,
  FirstName varchar2(20) not null,
  SecondName varchar2(20) not null,
  MiddleName varchar2(20) not null,
  Password varchar2(32) not null,
  Salt varchar2(10),
  Gender varchar2(20),
  Age number not null,
  constraint PK_UserInfo primary key (Id),
  constraint U_UserInfo unique(Login, FirstName, SecondName, MiddleName, Gender, Age)
);

/*----------------------CREATE TRIGGER ON INSERT UserInfo_TABLE---------------------*/
create or replace
trigger BI_UserInfo_TABLE 
before insert on UserInfo_TABLE
for each row
declare
salt varchar(10);
mmd5 varchar(32);
begin
  salt:=dbms_random.string('a',10);
  mmd5:= utl_raw.CAST_TO_RAW(dbms_obfuscation_toolkit.md5(input_string=>(:new.Password || salt)));
  select mmd5 into :new.Password from dual;
  select salt into :new.SALT from dual;
  select Get_ID_SEQUENCE.nextval into :new.ID from dual; --puts id >__<
end;
/

alter trigger BI_UserInfo_TABLE enable;
/*----------------------CREATE USER ENTITY---------------------*/
Create table User_TABLE
(
  ID number(*,0) not null,
  UserState number(*, 0) not null,
  UserInfo number(*, 0) unique not null,
  constraint PK_User primary key (ID),
  constraint FK_UserState foreign key (UserState) references UserState_TABLE(ID) on delete cascade,
  constraint FK_UserInfo foreign key (UserInfo) references UserInfo_TABLE(ID) on delete cascade
);


create or replace trigger  BI_User_TABLE
  before insert on User_TABLE
  for each row
begin
  select Get_ID_SEQUENCE.nextval into :new.ID from dual;
end;
/

alter trigger BI_User_TABLE enable;
/*----------------------CREATE COMMENTARY ENTITY---------------------*/
Create table Commentary_TABLE
(
  ID number(*, 0) not null,
  UserID number(*, 0) not null,
  ParentID number(*, 0),
  Text varchar2(140),
  constraint Commentary_PK primary key (ID),
  constraint Commentary_FK1 foreign key (UserID) references User_TABLE(ID) on delete cascade,
  constraint Commentary_FK2 foreign key (ParentID) references Commentary_TABLE(ID) on delete cascade
);


create or replace trigger  BI_Commentary_TABLE
  before insert on Commentary_TABLE
  for each row
begin
  select Get_ID_SEQUENCE.nextval into :new.ID from dual;
end;
/

alter trigger BI_Commentary_TABLE enable;

/*----------------------CREATE TYPES FOR NESTED TABLES---------------------*/
create type genre_id_list as table of varchar2(32);
/
create type actor_list as table of varchar2(64);
/

create directory covers_directory as 'C:\Images\Covers';
--create directory covers_directory as '/usr/lib/oracle/xe/oradata/lobs';   --this is unix-version:)


/*----------------------CREATE PROFIT VALUE OBJECT---------------------*/
create table Profit_TABLE
(
    ID number(*,0) not null,
    ProfitInRussia number,
    ProfitInUSA number,
    ProfitInEurope number,
    ProfitInOtherCountries number,
    ProfitFromTV number,
    ProfitFromDVD number,
    constraint PK_Profit primary key (ID),
    constraint U_Profit unique(ProfitInRussia, ProfitInUSA, ProfitInEurope, ProfitInOtherCountries, ProfitFromTV, ProfitFromDVD)
);


create or replace trigger  BI_Profit_TABLE
  before insert on Profit_TABLE
  for each row
begin
  select Get_ID_SEQUENCE.nextval into :new.ID from dual;
end;
/

alter trigger BI_Profit_TABLE enable;

/*----------------------CREATE MOVIE ENTITY---------------------*/
create table Movie_TABLE
(
    ID number(*,0) not null,
    Description varchar2(1024),
    IMDBRating number(4,2),
    MPAA varchar2(32),
    VotesRating number(10),
    Name varchar2(32),
    Genres genre_id_list,
	  Cover varchar2(256),
    Profit number(*,0),
    Actors actor_list,
    constraint PK_Movie primary key (ID),
    constraint FK_Movie_Profit foreign key (Profit) references Profit_TABLE(id) on delete cascade
)
NESTED table Genres STORE as Movie_x_Catalog_TABLE,
NESTED table Actors STORE as Movie_x_Actor_TABLE;


create or replace trigger  BI_Movie_TABLE
  before insert on Movie_TABLE
  for each row
begin
  select Get_ID_SEQUENCE.nextval into :new.ID from dual;
end;
/

alter trigger BI_Movie_TABLE enable;

/*----------------------CREATE EPISODE ENTITY---------------------*/
create table Episode_TABLE
(
    ID number(*,0) not null,
    Description varchar2(1024),
	IMDBRating number(4,2),
	MPAA varchar2(32),
	VotesRating number(10),
	Name varchar2(32),
    Genres genre_id_list,
	Cover varchar2(256),
    Profit number(*,0),
	Actors actor_list,
	constraint PK_Episode primary key (ID),
	constraint FK_Episode_Profit foreign key (Profit) references Profit_TABLE(id) on delete cascade
)
NESTED table Genres STORE as Episode_x_Catalog_TABLE,
NESTED table Actors STORE as Episode_x_Actor_TABLE;


create or replace trigger  BI_Episode_TABLE
  before insert on Episode_TABLE
  for each row
begin
  select Get_ID_SEQUENCE.nextval into :new.ID from dual;
end;
/

alter trigger BI_Episode_TABLE enable;

/*----------------------CREATE SERIES ENTITY---------------------*/
create table Series_TABLE
(
    ID number(*,0) not null,
    Description varchar2(1024),
    IMDBRating number(4,2),
    MPAA varchar2(32),
    VotesRating number(10),
    Name varchar2(32),
    Genres genre_id_list,
    Cover varchar2(256),
    Profit number(*,0),
    Actors actor_list,
    CountOfSeries number(4),
    constraint PK_Series primary key (ID),
    	constraint FK_Series_Profit foreign key (Profit) references Profit_TABLE(id) on delete cascade
)
NESTED table Genres STORE as Series_x_Catalog_TABLE,
NESTED table Actors STORE as Series_x_Actor_TABLE;

create or replace trigger  BI_Series_TABLE
  before insert on Series_TABLE
  for each row
begin
  select Get_ID_SEQUENCE.nextval into :new.ID from dual;
end;
/

alter trigger BI_Series_TABLE enable;

/*----------------------CREATE REVIEW ENTITY---------------------*/
create table Review_TABLE
(
   ID number(*, 0) not null,
   Text varchar2(4000) not null,  
   Rating number(*, 0) default (0),
   Author_ID number(*, 0) not null,  
   constraint PK_Review primary key (ID),
   constraint FK_Author  foreign key (Author_ID)  references User_TABLE(ID)  on delete cascade     
);

create or replace trigger  BI_Review_TABLE
  before insert on Review_TABLE
  for each row
begin
  select Get_ID_SEQUENCE.nextval into :new.ID from dual;
end;
/

alter trigger BI_Review_TABLE enable;
/*----------------------CREATE CATALOG ENTITY---------------------*/
create table Catalog_TABLE 
(
   ID number(*, 0) not null,
   NameOfGenre varchar2(32) not null, 
   Description varchar2(4000) not null,
   MPAARatingnumber number(*, 0) not null,
   Rating number(*,0),
   constraint PK_Catalog primary key (ID)
);

create or replace trigger  BI_Catalog_TABLE
  before insert on Catalog_TABLE
  for each row
begin
  select Get_ID_SEQUENCE.nextval into :new.ID from dual;
end;
/

alter trigger BI_Catalog_TABLE enable;

/*----------------------CREATE MOVIE TO COMMENTARIES LINKS TABLE---------------------*/
create table MovCom_Link_Table
(
   ID number(*, 0) not null,
   Movie_ID number(*,0),
   Commentary_ID number(*,0),
   constraint PK_MovCom_Link_Table primary key (ID),
   constraint FK_Mov_Link  foreign key (Movie_ID)  references Movie_TABLE(ID)  on delete cascade,
   constraint FK_Mov_Com_Link  foreign key (Commentary_ID) references Commentary_TABLE(ID)  on delete cascade     
);

create or replace trigger  BI_MovCom_Link_Table
  before insert on MovCom_Link_Table
  for each row
begin
  select Get_ID_SEQUENCE.nextval into :new.ID from dual;
end;
/

alter trigger BI_MovCom_Link_Table enable;
/*----------------------CREATE EPISODE TO COMMENTARIES LINKS TABLE---------------------*/
create table EpisodeCom_Link_Table
(
   ID number(*, 0) not null,
   Episode_ID number(*,0),
   Commentary_ID number(*,0),
   constraint PK_EpisodeCom_Link_Table primary key (ID),
   constraint FK_Episode_Link  foreign key (Episode_ID)  references Episode_TABLE(ID)  on delete cascade,
   constraint FK_Episode_Com_Link  foreign key (Commentary_ID) references Commentary_TABLE(ID)  on delete cascade     
);

create or replace trigger  BI_EpisodeCom_Link_Table
  before insert on EpisodeCom_Link_Table
  for each row
begin
  select Get_ID_SEQUENCE.nextval into :new.ID from dual;
end;
/

alter trigger BI_EpisodeCom_Link_Table enable;

/*----------------------CREATE Series TO COMMENTARIES LINKS TABLE---------------------*/
create table SeriesCom_Link_Table
(
   ID number(*, 0) not null,
   Series_ID number(*,0),
   Commentary_ID number(*,0),
   constraint PK_SeriesCom_Link_Table primary key (ID),
   constraint FK_Series_1_Link  foreign key (Series_ID)  references Series_TABLE(ID)  on delete cascade,
   constraint FK_Series_Com_Link  foreign key (Commentary_ID) references Commentary_TABLE(ID)  on delete cascade     
);

create or replace trigger  BI_SeriesCom_Link_Table
  before insert on SeriesCom_Link_Table
  for each row
begin
  select Get_ID_SEQUENCE.nextval into :new.ID from dual;
end;
/

alter trigger BI_SeriesCom_Link_Table enable;
/*----------------------CREATE Series TO EPISODES LINKS TABLE---------------------*/
create table SeriesEpisode_Link_Table
(
   ID number(*, 0) not null,
   Series_ID number(*,0),
   Episode_ID number(*,0),
   constraint PK_SeriesEpisode_Link_Table primary key (ID),
   constraint FK_Series_2_Link  foreign key (Series_ID)  references Series_TABLE(ID)  on delete cascade,
   constraint FK_Series_Episode_Link  foreign key (Episode_ID) references Episode_TABLE(ID)  on delete cascade     
);

create or replace trigger  BI_SeriesEpisode_Link_Table
  before insert on SeriesEpisode_Link_Table
  for each row
begin
  select Get_ID_SEQUENCE.nextval into :new.ID from dual;
end;
/

alter trigger BI_SeriesEpisode_Link_Table enable;

-- end :)
exit;